package com.quick.android.download;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.TextUtils;

/**
 * 相关数据库操作工具
 * 
 * @author quick
 * 
 */
public class DBHelper extends SQLiteOpenHelper {
	/** 数据库的名称 */
	private static final String DATEBASE_NAME = "download.db";
	/** 数据库的版本 */
	private static final int DATEBASE_VERSION = 1;
	/** 下载数据表名 */
	private final String tableDownload = "t_download";
	/** 下载线程信息表名 */
	private final String tableThreadEntity = "t_thread";
	/** 下载数据-建表语句 */
	private String sqlFileEntity = "CREATE TABLE "
			+ tableDownload
			+ "(id integer primary key autoincrement,"
			+ "d_id integer, d_url vchar(100),d_path vchar(100), d_length long, d_threads integer, d_range vchar(10),"
			+ "d_isSuccess vchar(10))";
	/** 下载线程信息-建表语句 */
	private String sqlThreadEntity = "CREATE TABLE " + tableThreadEntity
			+ "(id integer primary key autoincrement,"
			+ "t_id integer,t_start long,t_end long,t_load long)";

	public DBHelper(Context context) {
		super(context, DATEBASE_NAME, null, DATEBASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(sqlFileEntity);
		db.execSQL(sqlThreadEntity);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

	}
	/** 删除所有的下载线程信息数据 */
	public void deleteAllThreadEntity() {
		SQLiteDatabase database = getWritableDatabase();
		database.execSQL("drop table " + tableThreadEntity);
		database.execSQL(sqlThreadEntity);
		database.close();
	}

	/** 删除下载线程的数据 */
	public void deleteThreadEntity(int id) {
		SQLiteDatabase database = getWritableDatabase();
		database.delete(tableThreadEntity, "t_id = ?",
				new String[] { String.valueOf(id) });
		database.close();
	}

	public void saveOrUpdateThreadEntitys(List<ThreadInfoBean> list) {
		if (list == null || list.size() == 0) {
			return;
		}
		SQLiteDatabase database = getWritableDatabase();
		database.beginTransaction();
		int size = list.size();
		for (int i = 0; i < size; i++) {
			saveOrUpdateThreadEntity(database, list.get(i));
		}
		database.setTransactionSuccessful();
		database.endTransaction();
		if (database != null) {
			database.close();
		}

	}

	public void saveOrUpdateThreadEntity(SQLiteDatabase database,
			ThreadInfoBean entity) {
		if (entity == null)
			return;
		boolean isExist = false;
		Cursor cursor = database.query(tableThreadEntity, null, "t_id = "
				+ entity.id, null, null, null, null);
		if (cursor != null && cursor.moveToNext()) {
			isExist = true;
			cursor.close();
			cursor = null;
		}
		ContentValues values = new ContentValues();
		values.put("t_start", entity.start);
		values.put("t_end", entity.end);
		values.put("t_load", entity.load);
		if (isExist) {
			database.update(tableThreadEntity, values, "t_id = ?",
					new String[] { String.valueOf(entity.id) });
		} else {
			values.put("t_id", entity.id);
			database.insert(tableThreadEntity, null, values);
		}

	}

	public void deleteAllFileEntity() {
		SQLiteDatabase database = getWritableDatabase();
		database.execSQL("drop table " + tableDownload);
		database.execSQL(sqlFileEntity);
		database.close();
	}

	public void deleteFileEntityByUrl(String url) {
		if (TextUtils.isEmpty(url)) {
			return;
		}
		FileInfoBean entity = getEntityByUrl(url);
		deleteFileEntity(entity.url);
		if (entity.threadsEntities != null && entity.threadsEntities.size() > 0) {
			int size = entity.threadsEntities.size();
			for (int i = 0; i < size; i++) {
				deleteThreadEntity(entity.threadsEntities.get(i).id);
			}
		}
	}

	public void deleteFileEntity(String url) {
		if (TextUtils.isEmpty(url)) {
			return;
		}
		SQLiteDatabase database = getWritableDatabase();
		database.delete(tableDownload, "d_url = ?", new String[] { url });
		database.close();
	}

	public FileInfoBean getEntityByUrl(String url) {
		SQLiteDatabase database = getWritableDatabase();
		Cursor cursor = database.query(tableDownload, null, "d_url =?",
				new String[] { url }, null, null, null);
		FileInfoBean entity = null;
		if (cursor != null) {
			while (cursor.moveToNext()) {
				entity = new FileInfoBean();
				entity.id = cursor.getInt(cursor.getColumnIndex("d_id"));
				entity.url = cursor.getString(cursor.getColumnIndex("d_url"));
				entity.path = cursor
						.getString(cursor.getColumnIndex("d_path"));
				entity.threads = cursor.getInt(cursor
						.getColumnIndex("d_threads"));
				entity.length = cursor.getLong(cursor
						.getColumnIndex("d_length"));
				entity.range = Boolean.valueOf(cursor.getString(cursor
						.getColumnIndex("d_range")));
				entity.isSucess = Boolean.valueOf(cursor.getString(cursor
						.getColumnIndex("d_isSuccess")));
			}

		}
		if (entity != null) {
			cursor = database.query(tableThreadEntity, null, "t_id = "
					+ entity.id, null, null, null, null);
			List<ThreadInfoBean> list = new ArrayList<ThreadInfoBean>();
			ThreadInfoBean item = null;
			if (cursor != null) {
				while (cursor.moveToNext()) {
					item = new ThreadInfoBean();
					item.id = entity.id;
					item.start = cursor.getLong(cursor
							.getColumnIndex("t_start"));
					item.load = cursor.getLong(cursor
							.getColumnIndex("t_load"));
					item.end = cursor.getLong(cursor.getColumnIndex("t_end"));
					list.add(item);
				}
			}
			entity.threadsEntities = list;
		}
		if (cursor != null) {
			cursor.close();
			cursor = null;
		}
		if (database != null) {
			database.close();
		}
		return entity;
	}

	public List<FileInfoBean> getAllFinishEntity() {
		List<FileInfoBean> list = null;
		FileInfoBean entity = null;
		SQLiteDatabase database = getWritableDatabase();
		Cursor cursor = database.query(tableDownload, null,
				"d_isSuccess = true", null, null, null, null);
		if (cursor != null) {
			list = new ArrayList<FileInfoBean>();
			while (cursor.moveToNext()) {
				entity = new FileInfoBean();
				entity.id = cursor.getInt(cursor.getColumnIndex("d_id"));
				entity.url = cursor.getString(cursor.getColumnIndex("d_url"));
				entity.path = cursor
						.getString(cursor.getColumnIndex("d_path"));
				entity.threads = cursor.getInt(cursor
						.getColumnIndex("d_threads"));
				entity.length = cursor.getLong(cursor
						.getColumnIndex("d_length"));
				entity.range = Boolean.valueOf(cursor.getString(cursor
						.getColumnIndex("d_range")));
				entity.isSucess = Boolean.valueOf(cursor.getString(cursor
						.getColumnIndex("d_isSuccess")));
				list.add(entity);
			}
		}
		if (cursor != null) {
			cursor.close();
			cursor = null;
		}
		if (database != null) {
			database.close();
		}
		return list;
	}

	public List<FileInfoBean> getAllFailureEntity() {
		List<FileInfoBean> list = null;
		FileInfoBean entity = null;
		SQLiteDatabase database = getWritableDatabase();
		Cursor cursor = database.query(tableDownload, null,
				"d_isSuccess = false", null, null, null, null);
		if (cursor != null) {
			list = new ArrayList<FileInfoBean>();
			while (cursor.moveToNext()) {
				entity = new FileInfoBean();
				entity.id = cursor.getInt(cursor.getColumnIndex("d_id"));
				entity.url = cursor.getString(cursor.getColumnIndex("d_url"));
				entity.path = cursor
						.getString(cursor.getColumnIndex("d_path"));
				entity.threads = cursor.getInt(cursor
						.getColumnIndex("d_threads"));
				entity.length = cursor.getLong(cursor
						.getColumnIndex("d_length"));
				entity.range = Boolean.valueOf(cursor.getString(cursor
						.getColumnIndex("d_range")));
				entity.isSucess = Boolean.valueOf(cursor.getString(cursor
						.getColumnIndex("d_isSuccess")));
				list.add(entity);
			}
		}
		if (cursor != null) {
			cursor.close();
			cursor = null;
		}
		if (database != null) {
			database.close();
		}
		return list;
	}

	public List<FileInfoBean> getAllEntity() {
		List<FileInfoBean> list = null;
		FileInfoBean entity = null;
		SQLiteDatabase database = getWritableDatabase();
		Cursor cursor = database.query(tableDownload, null, null, null, null,
				null, null);
		if (cursor != null) {
			list = new ArrayList<FileInfoBean>();
			while (cursor.moveToNext()) {
				entity = new FileInfoBean();
				entity.id = cursor.getInt(cursor.getColumnIndex("d_id"));
				entity.url = cursor.getString(cursor.getColumnIndex("d_url"));
				entity.path = cursor
						.getString(cursor.getColumnIndex("d_path"));
				entity.threads = cursor.getInt(cursor
						.getColumnIndex("d_threads"));
				entity.length = cursor.getLong(cursor
						.getColumnIndex("d_length"));
				entity.range = Boolean.valueOf(cursor.getString(cursor
						.getColumnIndex("d_range")));
				entity.isSucess = Boolean.valueOf(cursor.getString(cursor
						.getColumnIndex("d_isSuccess")));
				list.add(entity);
			}
		}
		if (cursor != null) {
			cursor.close();
			cursor = null;
		}
		if (database != null) {
			database.close();
		}
		return list;
	}

	public void saveOrUpdateFileEntity(FileInfoBean entity) {
		SimpleUtils.e("saveOrUpdateFileEntity");
		if (entity == null)
			return;
		boolean isExist = false;
		SQLiteDatabase database = getWritableDatabase();
		Cursor cursor = database.query(tableDownload, null, "d_id = "
				+ entity.id, null, null, null, null);
		if (cursor != null && cursor.moveToNext()) {
			isExist = true;
			cursor.close();
			cursor = null;
		}
		ContentValues values = new ContentValues();
		values.put("d_url", entity.url);
		values.put("d_path", entity.path);
		values.put("d_length", entity.length);
		values.put("d_threads", entity.threads);
		values.put("d_range", String.valueOf(entity.range));
		values.put("d_isSuccess", String.valueOf(entity.isSucess));
		SimpleUtils.e("saveOrUpdateFileEntity:isExist:" + isExist);
		if (isExist) {
			SimpleUtils.e("saveOrUpdateFileEntity: 更新数据成功");
			database.update(tableDownload, values, "d_id = ?",
					new String[] { String.valueOf(entity.id) });
		} else {
			values.put("d_id", entity.id);
			SimpleUtils.e("saveOrUpdateFileEntity: 插入数据成功");
			database.insert(tableDownload, null, values);
		}
		database.close();
	}

	public void updateFileEntity(FileInfoBean entity) {
		if (entity == null)
			return;
		SQLiteDatabase database = getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("d_url", entity.url);
		values.put("d_path", entity.path);
		values.put("d_length", entity.length);
		values.put("d_threads", entity.threads);
		values.put("d_range", String.valueOf(entity.range));
		values.put("d_isSuccess", String.valueOf(entity.isSucess));
		database.update(tableDownload, values, "d_id = ?",
				new String[] { String.valueOf(entity.id) });
		database.close();
	}

}
